﻿CREATE PROCEDURE [maint].[RestoreFromDevice]
@SourceDbName [sysname], @SourceDeviceName NVARCHAR (128)=NULL, @TargetDbName [sysname]=NULL, @RestorePoint DATETIME=NULL, @OutputDiagnostics BIT=0
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @RC int, @ERROR int
	SET @RC = 0

	IF(@OutputDiagnostics = 1)
		BEGIN
			PRINT OBJECT_NAME(@@PROCID)
			PRINT N'	@SourceDbName = N''' + ISNULL(@SourceDbName,N'NULL') + ''''
			PRINT N'	@SourceDeviceName = N''' + ISNULL(@SourceDeviceName,N'NULL') + ''''
			PRINT N'	@TargetDbName = N''' + ISNULL(@TargetDbName,N'NULL') + ''''
			PRINT N'	@RestorePoint = N''' + ISNULL(CAST(@RestorePoint AS nvarchar(20)),N'NULL') + ''''
		END

	IF(@SourceDeviceName IS NULL)
		BEGIN
			SET @SourceDeviceName = maint.GetDeviceName(@SourceDbName);
			IF(@OutputDiagnostics = 1)
				PRINT N'	@SourceDeviceName set to N''' + ISNULL(@SourceDeviceName,N'NULL') + ''''
		END;

	IF(@TargetDbName IS NULL)
		SET @TargetDbName = @SourceDbName;

	DECLARE @FullBackup int, @DiffBackup int, @LogBackup int
	SET @FullBackup = 1;
	SET @DiffBackup = 5;
	SET @LogBackup = 2;

	DECLARE @CurrentPosition int, @NextPosition int, @NextBackupType int, @Recover bit
	SET @Recover = 1;

	BEGIN TRY

		/* FULL RESTORE */
		EXEC @RC = [maint].[GetDeviceRestoreSet]
			 @DbName = @SourceDbName
			,@DeviceName = @SourceDeviceName
			,@CurrentPosition = NULL
			,@BackupType = @FullBackup
			,@RestorePoint = @RestorePoint
			,@NextPosition = @NextPosition OUTPUT
			,@OutputDiagnostics = @OutputDiagnostics;

		IF(@NextPosition IS NULL)
			RAISERROR('Device %s does not contain a valid database backup',16,1,@SourceDeviceName);
		
		DECLARE @FullPosition int, @DiffPosition int, @LogPosition int
		SET @FullPosition = @NextPosition;

		--get first differential
		SET @NextBackupType = @DiffBackup;
		EXEC @RC = [maint].[GetDeviceRestoreSet]
			 @DbName = @SourceDbName
			,@DeviceName = @SourceDeviceName
			,@CurrentPosition = @FullPosition
			,@BackupType = @DiffBackup
			,@RestorePoint = @RestorePoint
			,@NextPosition = @NextPosition OUTPUT
			,@OutputDiagnostics = @OutputDiagnostics;

		IF(@NextPosition IS NOT NULL)
			BEGIN
				SET @DiffPosition = @NextPosition;
				SET @Recover = 0;
			END;

		--get first log
		SET @NextBackupType = @LogBackup;
		SET @CurrentPosition = ISNULL(@DiffPosition,@FullPosition);
		EXEC @RC = [maint].[GetDeviceRestoreSet]
			 @DbName = @SourceDbName
			,@DeviceName = @SourceDeviceName
			,@CurrentPosition = @CurrentPosition
			,@BackupType = @LogBackup
			,@RestorePoint = @RestorePoint
			,@NextPosition = @NextPosition OUTPUT
			,@OutputDiagnostics = @OutputDiagnostics;

		IF(@NextPosition IS NOT NULL)
			BEGIN
				SET @LogPosition = @NextPosition;
				SET @Recover = 0;
			END;

		--restore the full backup
		EXEC @RC = [maint].[RestoreFullFromDevice]
			 @DbName = @TargetDbName
			,@DeviceName = @SourceDeviceName
			,@FilePosition = @FullPosition
			,@Recover = @Recover
			,@OutputDiagnostics = @OutputDiagnostics;		

		/* DIFFERENTIAL RESTORE */
		IF (@DiffPosition IS NOT NULL)
			BEGIN
				IF(@LogPosition IS NULL)
					SET @Recover = 1;

				EXEC @RC = [maint].[RestoreDiffFromDevice]
					 @DbName = @TargetDbName
					,@DeviceName = @SourceDeviceName
					,@FilePosition = @CurrentPosition
					,@Recover = @Recover
					,@OutputDiagnostics = @OutputDiagnostics;
			END;

		/* LOG RESTORES */
		WHILE(@LogPosition IS NOT NULL)
		  AND(@Recover = 0)
			BEGIN
				--get next log
				DECLARE @BackupStartDate datetime, @BackupFinishDate datetime;

				EXEC @RC = [maint].[GetDeviceRestoreSet] 
					 @DbName = @SourceDbName
					,@DeviceName = @SourceDeviceName
					,@CurrentPosition = @LogPosition
					,@BackupType = @LogBackup
					,@RestorePoint = @RestorePoint
					,@NextPosition = @NextPosition OUTPUT
					,@BackupStartDate = @BackupStartDate OUTPUT
					,@BackupFinishDate = @BackupFinishDate OUTPUT
					,@OutputDiagnostics = @OutputDiagnostics;

				IF(@NextPosition IS NULL)
					SET @Recover = 1;

				DECLARE @StopAtTime datetime
				SET @StopAtTime = (CASE
					WHEN (@RestorePoint BETWEEN @BackupStartDate AND @BackupFinishDate) THEN @RestorePoint
					ELSE NULL
				END);

				EXEC @RC = [maint].[RestoreLogFromDevice]
					 @DbName = @TargetDbName
					,@DeviceName = @SourceDeviceName
					,@FilePosition = @LogPosition
					,@Recover = @Recover
					,@StopAtTime = @StopAtTime
					,@OutputDiagnostics = @OutputDiagnostics;

				SET @LogPosition = @NextPosition;
			END;

	END TRY
	BEGIN CATCH
		RAISERROR('Failed to restore target database %s',16,1,@TargetDbName);
		DECLARE @ErrorOutput nvarchar(max);
		EXEC [util].[GetErrorOutput]
			@Output = @ErrorOutput OUTPUT;
		PRINT @ErrorOutput;
	END CATCH

	RETURN(@RC)
END;